/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package expenseslist;

import java.sql.*;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import javax.swing.JList;
import javax.swing.JOptionPane;

/**
 *
 * @author trunghpb
 */
public class ExpenseDB {
    MySqlDb mysqlDb;
    
    public ExpenseDB(String name, String des)throws Exception {
        setName(name);
        setDesc(des);
        init();
    }

    public ExpenseDB() throws Exception {
        init();
    }
        
    public ExpenseDB(String name, double amounth) throws Exception{
        setName(name);
        setAmounth(amounth);
        init();
    }

    public void init() throws Exception{
        reset();
        mysqlDb = new MySqlDb();
        mysqlDb.connect();
    }
    
    public ExpenseDB reset(){
        _name = "";
        _description = "";
        _amounth = 0;        
        _date = "";
        _account = null;
        return this;
    }
    
    public ExpenseDB setName(String name){
        _name = name;
        return this;
    }
    
    public ExpenseDB setDesc(String des){
        _description = des;
        return this;
    }

    public ExpenseDB setDate(String date){
        _date = date;
        return this;
    }
    
    public ExpenseDB setAmounth(double amounth){
        _amounth = amounth;
        return this;
    }

    public ExpenseDB setAccount(AccountDB account){
        _account = account;
        return this;
    }
    
    public int getID(){
        return _id;
    }
    
    public double getAmount(){
        return _amounth;
    }
    
    public String getName(){
        return _name;
    }
    
    public String getDescription(){
        return _description;
    }
    
    public String getDate(){
        return _date;
    }
    
    public AccountDB getAccountName(){
        return _account;
    }
        
    public int findID(int id){return SUCCESS;}
    
    public int save(){ return SUCCESS;}
    
    public int insert() throws SQLException{ 
        String sql = "";

        sql += "INSERT INTO expenses"
                + "(expenses_amounth, expenses_name, "
                + "expenses_descritption, expenses_date)"
                + "VALUES (?, ?, ?, ?)";
         
        PreparedStatement statement = mysqlDb.getPrepareStatement(sql);
        statement.setDouble(1, _amounth);
        statement.setString(2, _name);
        statement.setString(3, _description);
        statement.setString(4, _date);
        
        mysqlDb.execute(statement);
    
        ResultSet rs = statement.getGeneratedKeys();
        
        if (rs.next()){
            _id = rs.getInt(1);
        }

        String sqlAccount = "INSERT INTO account_expenses(account_expenses_account_id, account_expenses_expenses_id)"
                + "VALUES (?, ?)";

        PreparedStatement stateAccount = mysqlDb.getPrepareStatement(sqlAccount);
        stateAccount.setInt(1, _account.getID());
        stateAccount.setInt(2, _id);

        mysqlDb.execute(stateAccount);
        
        return SUCCESS;
        
    }
    
    public int update(){
        return SUCCESS;
    }
    
    public ResultSet find() throws SQLException{
        
        String sql = ""; 
        sql +=    "SELECT expenses_id, expenses_amounth, expenses_name, expenses_descritption, expenses_date,"
                + "       account_id, account_name, account_description "
                + "FROM expenses "
                + "LEFT JOIN account_expenses ON account_expenses_expenses_id = expenses_id "
                + "LEFT JOIN account ON account_id = account_expenses_account_id "
                + "WHERE expenses_deleted_flag = 0 ";
        
        ArrayList<Object> list = new ArrayList<Object>();
        ArrayList<Integer> typeList = new ArrayList<Integer>();
        
        if (_account != null){
            list.add(_account.getID());
            typeList.add(TYPE_INT);
            sql += " AND account_id = ?";
        }
        
        if (!_date.isEmpty() && _date != null){
            list.add(_date);
            typeList.add(TYPE_STR);
            sql += " AND expenses_date >= ?";
        }
        
        PreparedStatement stateAccount = mysqlDb.getPrepareStatement(sql);
        for (int i = 1; i<= list.size(); i++){
            int type = typeList.get(i-1);
            if (type == TYPE_INT){
                stateAccount.setInt(i, Integer.parseInt(list.get(i-1).toString()));
            }else if (type == TYPE_STR){
                stateAccount.setString(i, list.get(i-1).toString());
            }
        }
        
        ResultSet rs = stateAccount.executeQuery();
        //ResultSet rs = stateAccount.getResultSet();
        
        return rs;
    }
    
    public static final int SUCCESS = 1;
    public static final int DB_FAILURE = 2;
    public static final int TYPE_INT = 1;
    public static final int TYPE_STR = 2;
    
    private int _id;
    private double _amounth;
    private String _name;
    private String _description;
    private String _date;
    private AccountDB _account;
}
